﻿using NPOI;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace Excel
{
    /// <summary>
    /// excel 读取类
    /// </summary>
    public class ExcelRead
    {
        ExcelHelper helper;

        public ExcelRead(string path)
        {
            helper = new ExcelHelper(path);
        }

        public string[] GetSheetNames()
        {

            string[] names = new string[helper.SheetNumber];

            for (int i = 0; i < helper.SheetNumber; i++)
            {
                names[i] = helper.GetSheetName(i);
            }

            return names;
        }
        public bool IsSheel(string sheel)
        {
            bool have = false;
            for (int i = 0; i < helper.SheetNumber; i++)
            {
                if (helper.GetSheetName(i).Equals(sheel))
                {
                    have = true;
                    break;
                }

            }
            return have;
        }

        /// <summary>
        /// 读取表全部数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable Read(string tableName)
        {
            var sheet = helper.GetSheet(tableName);
            return helper.ExportToDataTable(sheet);
        }


        /// <summary>
        /// 读取指定数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columName"></param>
        /// <returns></returns>
        public DataTable Read(string tableName, string[] columName, out int[] index)
        {
            var sheet = helper.GetSheet(tableName);
            return Read(sheet, columName, out index);
        }
        /// <summary>
        /// 读取指定数据
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columIndex"></param>
        /// <returns></returns>
        public DataTable Read(string tableName, int[] columIndex)
        {
            var sheet = helper.GetSheet(tableName);
            return Read(sheet, 0, columIndex);
        }

        /// <summary>
        /// 读取指定数据
        /// </summary>
        /// <param name="tableIndex"></param>
        /// <param name="columName"></param>
        /// <returns></returns>
        public DataTable Read(int tableIndex, string[] columName, out int[] index)
        {
            var sheet = helper.GetSheet(tableIndex);
            return Read(sheet, columName, out index);
        }
        /// <summary>
        /// 读取指定数据
        /// </summary>
        /// <param name="tableIndex"></param>
        /// <param name="columIndex"></param>
        /// <returns></returns>
        public DataTable Read(int tableIndex, int[] columIndex)
        {
            var sheet = helper.GetSheet(tableIndex);
            return Read(sheet, 0, columIndex);
        }


        private DataTable Read(ISheet sheet, int startIndex, int[] columIndex)
        {
            if (sheet == null)
            {
                // TODO  错误
                throw new Exception("Sheet 未找到");
            }
            DataTable dt = null;

            try
            {
                for (int i = startIndex; i < sheet.PhysicalNumberOfRows; i++)
                {
                    var cell = sheet.GetRow(i);
                    if (cell == null)
                        break;

                    if (dt == null)
                    {
                        dt = new DataTable();
                        for (int j = 0; j < columIndex.Length; j++)
                        {
                            int index = columIndex[j];
                            if (index == -1)
                                continue;

                            var cellvalue = cell.GetCell(index);

                            var value = helper.GetCellValue(cellvalue);
                            dt.Columns.Add(value);
                        }
                    }
                    else
                    {
                        DataRow dataRow = dt.NewRow();

                        bool rowNull = true;
                        for (int j = 0; j < columIndex.Length; j++)
                        {
                            if (columIndex[j] == -1)
                                continue;

                            ICell cellValue = cell.GetCell(columIndex[j]);
                            if (cellValue != null)
                            {
                                if (cellValue.IsMergeCell(out Dimension dimension))
                                    cellValue = dimension.DataCell;

                                dataRow[j] = CellToValue(cellValue, cellValue.CellType);
                                rowNull = false;
                            }
                        }

                        if (dataRow != null && !rowNull)
                            dt.Rows.Add(dataRow);
                    }
                }

                return dt;

            }
            catch (Exception e)
            {

                Console.WriteLine(e.Message);

                return null;
            }
        }


        private object CellToValue(ICell cellValue, CellType celltype)
        {
            switch (celltype)
            {
                case CellType.String:
                    return cellValue.StringCellValue.Trim();
                case CellType.Numeric:

                    if (DateUtil.IsCellDateFormatted(cellValue))
                        return cellValue.DateCellValue;

                    return cellValue.NumericCellValue;
                case CellType.Boolean:
                    return cellValue.BooleanCellValue;
                case CellType.Formula:
                    var type = cellValue.GetType();

                    CellType hssType;
                    if (cellValue is XSSFCell)
                    {
                        hssType = ((XSSFCell)cellValue).CachedFormulaResultType;
                    }
                    else if (cellValue is HSSFCell)
                    {
                        hssType = ((HSSFCell)cellValue).CachedFormulaResultType;
                    }
                    else
                    {
                        return "error";
                    }

                    return CellToValue(cellValue, hssType);

                default:
                    return null;
            }
        }

        private DataTable Read(ISheet sheet, string[] columName, out int[] index)
        {
            if (sheet == null)
            {
                // TODO  错误
                throw new Exception("Sheet 未找到");
            }

            var listColum = columName.ToList();
            var copyColum = columName.ToList();
            index = new int[columName.Length];

            for (int i = 0; i < index.Length; i++)
                index[i] = -1;

            if (sheet.PhysicalNumberOfRows < 1)
            {
                return null;
                // TODO 行数错误
                //throw new Exception(sheet.SheetName + ": 行数错误, 行数为0");
            }

            int h = 0;

            do
            {
                if (h >= sheet.PhysicalNumberOfRows)
                    break;

                var title = sheet.GetRow(h++);
                if (title == null)
                    break;
                for (int i = 0; i < title.PhysicalNumberOfCells; i++)
                {
                    var cell = title.GetCell(i);

                    if (cell == null)
                        continue;

                    string value = helper.GetCellValue(cell).Trim();
                    for (int j = 0; j < listColum.Count; j++)
                    {
                        var v = listColum[j].Trim();
                        if (value.Equals(v))
                        {
                            int id = copyColum.FindIndex(name => name.Equals(v));
                            index[id] = i;
                            listColum.RemoveAt(j);
                            break;
                        }
                    }
                    if (listColum.Count == 0)
                        break;
                }

            } while (listColum.Count != 0);

            return Read(sheet, h - 1, index);
        }
    }
}